﻿using CSRedis;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using QskjLibrary;
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;

namespace QskjTotalData
{
    public class DataAc
    {
        public static void UpdateAlarmMonthData(DateTime dts, DateTime dte)
        {
            List<CurrentAlarmType> types = new List<CurrentAlarmType>();
            DateTime startTime = dts;//第一天
            DateTime endTime = dte;
            for (DateTime time = startTime; time <= endTime; time = time.AddDays(1))
            {
                try
                {
                    string dtStr = time.ToString("yyyyMMdd");
                    //string sql1 = $@"select count(*)num from qskjrecord.B_Vehicle_Alarm{dtStr};";
                    string sql1 = $@"select count(*)num from ( select devGbId,alarmId from qskjrecord.B_Vehicle_Alarm{dtStr} group by devGbId,alarmId) a;";
                    object res1 = MySqlHelper.ExecuteScalar(CommandType.Text, sql1);
                    int num1 = Convert.ToInt32(res1);

                    string sql2 = $@"select alarmType from  qskjrecord.B_Vehicle_Alarm{dtStr} a 
                                            join qskj.B_Bus as b on a.vehicleNo=b.license_plate
                                             group by alarmType ;";
                    DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                    foreach (DataRow item in res2.Rows)
                    {
                        CurrentAlarmType type = new CurrentAlarmType();
                        type.type = item["alarmType"] == DBNull.Value ? "" : item["alarmType"].ToString();
                        type.boc = "b";
                        types.Add(type);
                    }
                    string sql3 = $@"select alarmType from  qskjrecord.B_Vehicle_Alarm{dtStr} a 
                                            join qskj.B_Taxi as b on a.vehicleNo=b.license_plate
                                             group by alarmType ;";
                    DataTable res3 = MySqlHelper.GetDataTable(CommandType.Text, sql3);
                    foreach (DataRow item in res3.Rows)
                    {
                        CurrentAlarmType type = new CurrentAlarmType();
                        type.type = item["alarmType"] == DBNull.Value ? "" : item["alarmType"].ToString();
                        type.boc = "c";
                        types.Add(type);
                    }
                    int currentMonthWarningType = types.Where(p => p.boc == "b").Select(p => p.type).Distinct().Count() + types.Where(p => p.boc == "c").Select(p => p.type).Distinct().Count();
                    int currentMonthWarningArtificial = 0;
                    {
                        string sql11 = $@"select count(*)num from ( select devGbId,alarmId from qskjrecord.B_Vehicle_Alarm{dtStr} where (( platform='RUIMING' and alarmType=1)or(platform='RUIMING' and alarmType=2)or(platform='zl' and alarmType=6574)) group by devGbId,alarmId) a;";
                        object res11 = MySqlHelper.ExecuteScalar(CommandType.Text, sql11);
                        currentMonthWarningArtificial = Convert.ToInt32(res11);
                    }
                    string sqlIn = $@"insert into qskj.D_AlarmDataMonth(dataNum,typeNum,confirmNum,dayDate) values({num1},{currentMonthWarningType},{currentMonthWarningArtificial},'{time.ToString("yyyy-MM-dd")}')";
                    int res = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                    if (res == 0)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "汇总数据写入结果：" + res);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "汇总数据写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        public static void UpdateAlarmMonthDataForFiliale(DateTime dts, DateTime dte)
        {
            List<CurrentAlarmType> types = new List<CurrentAlarmType>();
            DateTime startTime = dts;//第一天
            DateTime endTime = dte;
            string dtStr = startTime.ToString("yyyyMMdd");
            List<string> depList = new List<string>();
            {
                string sqlDep = $@"select dep from qskjrecord.B_BusZT{dtStr} group by dep;";
                DataTable dtDep = MySqlHelper.GetDataTable(CommandType.Text, sqlDep);
                foreach (DataRow item in dtDep.Rows)
                {
                    depList.Add(item["dep"].ToString());
                }
            }
            foreach (var dep in depList)
            {
                try
                {
                    string sql1 = $@"select count(*)num from (select devGbId,alarmId from qskjrecord.B_Vehicle_Alarm{dtStr} aa join qskjrecord.B_BusZT{dtStr} bb on aa.vehicleNo=bb.car_numbers where dep='{dep}' group by devGbId,alarmId) a;";
                    object res1 = MySqlHelper.ExecuteScalar(CommandType.Text, sql1);
                    int num1 = Convert.ToInt32(res1);

                    string sql2 = $@"select alarmType from  qskjrecord.B_Vehicle_Alarm{dtStr} a 
                                            join qskj.B_Bus as b on a.vehicleNo=b.license_plate
                                            join qskjrecord.B_BusZT{dtStr} c on a.vehicleNo=c.car_numbers where dep='{dep}'
                                            group by alarmType ;";
                    DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                    foreach (DataRow item in res2.Rows)
                    {
                        CurrentAlarmType type = new CurrentAlarmType();
                        type.type = item["alarmType"] == DBNull.Value ? "" : item["alarmType"].ToString();
                        type.boc = "b";
                        types.Add(type);
                    }
                    string sql3 = $@"select alarmType from  qskjrecord.B_Vehicle_Alarm{dtStr} a 
                                            join qskj.B_Taxi as b on a.vehicleNo=b.license_plate
                                            join qskjrecord.B_BusZT{dtStr} c on a.vehicleNo=c.car_numbers where dep='{dep}'
                                            group by alarmType ;";
                    DataTable res3 = MySqlHelper.GetDataTable(CommandType.Text, sql3);
                    foreach (DataRow item in res3.Rows)
                    {
                        CurrentAlarmType type = new CurrentAlarmType();
                        type.type = item["alarmType"] == DBNull.Value ? "" : item["alarmType"].ToString();
                        type.boc = "c";
                        types.Add(type);
                    }
                    int currentMonthWarningType = types.Where(p => p.boc == "b").Select(p => p.type).Distinct().Count() + types.Where(p => p.boc == "c").Select(p => p.type).Distinct().Count();
                    int currentMonthWarningArtificial = 0;
                    {
                        string sql11 = $@"select count(*)num from ( select devGbId,alarmId from qskjrecord.B_Vehicle_Alarm{dtStr} aa join qskjrecord.B_BusZT{dtStr} bb on aa.vehicleNo=bb.car_numbers where dep='{dep}' and (( platform='RUIMING' and alarmType=1)or(platform='RUIMING' and alarmType=2)or(platform='zl' and alarmType=6574)) group by devGbId,alarmId) a;";
                        object res11 = MySqlHelper.ExecuteScalar(CommandType.Text, sql11);
                        currentMonthWarningArtificial = Convert.ToInt32(res11);
                    }
                    string sqlIn = $@"insert into qskj.D_AlarmDataMonthFiliale(dataNum,typeNum,confirmNum,dayDate,filialename) values({num1},{currentMonthWarningType},{currentMonthWarningArtificial},'{startTime.ToString("yyyy-MM-dd")}','{dep}')";
                    int res = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                    if (res == 0)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "汇总数据写入结果：" + res);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "汇总数据写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        public static void UpdateAlarmDayData(DateTime dts, DateTime dte)
        {
            List<CurrentAlarmType> types = new List<CurrentAlarmType>();
            DateTime startTime = dte.AddHours(-1);//当天0点
            DateTime endTime = dte;//当前时间
            for (DateTime time = startTime; time < endTime; time = time.AddHours(1))
            {
                try
                {
                    string dtStr = startTime.ToString("yyyyMMdd");
                    //string sqlf = $@"select count(*)num from qskjrecord.B_Vehicle_Alarm{dtStr} where createtime>='{time.ToString("yyyy-MM-dd HH:mm:ss")}' and createtime<'{time.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}' ;";
                    string sqlf = $@"select count(*)num from (select devGbId,alarmId from qskjrecord.B_Vehicle_Alarm{dtStr} where createtime>='{time.ToString("yyyy-MM-dd HH:mm:ss")}' and createtime<'{time.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}'  group by devGbId,alarmId) a;";
                    object resf = MySqlHelper.ExecuteScalar(CommandType.Text, sqlf);
                    int num1 = Convert.ToInt32(resf);
                    int currentMonthWarningArtificial = 0;
                    {
                        string sql11 = $@"select count(*)num from (select aa.* from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                          join (
			                                    select alarmid,vehicleNo,max(id)id from qskjrecord.B_Vehicle_Alarm{dtStr} group by alarmId,vehicleNo
                                               ) ab on aa.id=ab.id
                                               where aa.platform!='ZAKJ'
                                    ) as a
                                    join qskj.R_Alarm_Type c on a.alarmType=c.typeId where c.qskjId='47' and c.busOrCar='c' and createtime>='{time.ToString("yyyy-MM-dd HH:mm:ss")}' and createtime<'{time.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}'  ;";
                        object res11 = MySqlHelper.ExecuteScalar(CommandType.Text, sql11);
                        currentMonthWarningArtificial = Convert.ToInt32(res11);
                    }
                    string sqlIn = $@"insert into qskj.D_AlarmToDayHour(hourNum,typeNum,confirmNum,dayDate) values({num1},0,{currentMonthWarningArtificial},'{time.ToString("yyyy-MM-dd HH:mm:ss")}')";
                    int res = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                    if (res == 0)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日报警汇总数据小时段写入结果：" + res);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日报警汇总数据小时段写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        public static void UpdateAlarmDayDataForFiliale(DateTime dts, DateTime dte)
        {
            List<CurrentAlarmType> types = new List<CurrentAlarmType>();
            DateTime startTime = dte.AddHours(-1);//当天0点
            DateTime endTime = dte;//当前时间
            string dtStr = startTime.ToString("yyyyMMdd");
            List<string> depList = new List<string>();
            {
                string sqlDep = $@"select dep from qskjrecord.B_BusZT{dtStr} group by dep;";
                DataTable dtDep = MySqlHelper.GetDataTable(CommandType.Text, sqlDep);
                foreach (DataRow item in dtDep.Rows)
                {
                    depList.Add(item["dep"].ToString());
                }
            }
            foreach (var dep in depList)
            {
                try
                {
                    string sqlf = $@"select count(*)num from (select devGbId,alarmId from qskjrecord.B_Vehicle_Alarm{dtStr} aa join qskjrecord.B_BusZT{dtStr} bb on aa.vehicleNo=bb.car_numbers where bb.dep='{dep}' and aa.createtime>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and aa.createtime<'{startTime.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}'  group by devGbId,alarmId) a;";
                    object resf = MySqlHelper.ExecuteScalar(CommandType.Text, sqlf);
                    int num1 = Convert.ToInt32(resf);
                    int currentMonthWarningArtificial = 0;
                    {
                        string sql11 = $@"select count(*)num from (select aa.* from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                          join (
			                                    select alarmid,vehicleNo,max(id)id from qskjrecord.B_Vehicle_Alarm{dtStr} group by alarmId,vehicleNo
                                               ) ab on aa.id=ab.id
                                               where aa.platform!='ZAKJ'
                                    ) as a
                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                    join qskj.R_Alarm_Type c on a.alarmType=c.typeId where b.dep='{dep}'and c.qskjId='47' and c.busOrCar='c' and createtime>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and createtime<'{startTime.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}'  ;";
                        //string sql11 = $@"select count(*)num from ( select devGbId,alarmId from qskjrecord.B_Vehicle_Alarm{dtStr} aa 
                        //                    join qskjrecord.B_BusZT{dtStr} bb on aa.vehicleNo=bb.car_numbers 
                        //                    where bb.dep='{dep}'and  (( platform='RUIMING' and alarmType=1)or(platform='RUIMING' and alarmType=2)or(platform='zl' and alarmType=6574)) 
                        //                    and aa.createtime>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' 
                        //                    and aa.createtime<'{startTime.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}' 
                        //                    group by devGbId,alarmId) a;";
                        object res11 = MySqlHelper.ExecuteScalar(CommandType.Text, sql11);
                        currentMonthWarningArtificial = Convert.ToInt32(res11);
                    }
                    int piLao = 0;//疲劳数据总数
                    {
                        string sql11 = $@"select count(*)num from (select aa.* from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                          join (
			                                    select alarmid,vehicleNo,max(id)id from qskjrecord.B_Vehicle_Alarm{dtStr} group by alarmId,vehicleNo
                                               ) ab on aa.id=ab.id 
                                    ) as a
                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                    join qskj.R_Alarm_Type c on a.alarmType=c.typeId where b.dep='{dep}' and c.qskjId=3 and ((a.platform='ZAKJ' and c.busorcar='b')or(a.platform!='ZAKJ' and c.busorcar='c')) and createtime>='{startTime.ToString("yyyy-MM-dd HH:mm:ss")}' and createtime<'{startTime.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}'  ;";
                        object res11 = MySqlHelper.ExecuteScalar(CommandType.Text, sql11);
                        piLao = Convert.ToInt32(res11);
                    }
                    string sqlIn = $@"insert into qskj.D_AlarmToDayHourFiliale(hourNum,typeNum,confirmNum,dayDate,filialename) values({num1},{piLao},{currentMonthWarningArtificial},'{startTime.ToString("yyyy-MM-dd HH:mm:ss")}','{dep}')";
                    int res = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                    if (res == 0)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日车队报警汇总数据小时段写入结果：" + res);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日车队报警汇总数据小时段写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        public static void UpdateCompanyAlarmDayData(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间
            try
            {
                string dtStr = DateTime.Now.ToString("yyyyMMdd");
                List<DepAnalyze> depList = new List<DepAnalyze>();
                {
                    string sqlDep = $@"select car_assets_name_name,dep,count(*)num  from qskjrecord.B_BusZT{dtStr} group by car_assets_name_name,dep;";
                    DataTable dtDep = MySqlHelper.GetDataTable(CommandType.Text, sqlDep);
                    foreach (DataRow item in dtDep.Rows)
                    {
                        DepAnalyze depAnalyze = new DepAnalyze();
                        depAnalyze.company = item["car_assets_name_name"].ToString();
                        depAnalyze.dep = item["dep"].ToString();
                        depAnalyze.num = Convert.ToInt32(item["num"]);
                        depList.Add(depAnalyze);
                    }
                }
                {
                    try
                    {
                        List<AlarmVehicleCompanyByToday> res = new List<AlarmVehicleCompanyByToday>();
                        {
                            foreach (var dep in depList)
                            {
                                string sqlBus = $@"select b.business_unit,count(*)num from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
										join qskj.B_Bus d on a.vehicleNo=d.license_plate 
										where c.threeClassId='1' and c.busOrCar='b'  and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{endTime.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.business_unit;";
                                DataTable resBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                                foreach (DataRow item in resBus.Rows)
                                {
                                    AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                    type.time = startTime.ToString("yyyy-MM-dd");
                                    type.company = item["business_unit"] == DBNull.Value ? "" : item["business_unit"].ToString();
                                    type.num = Convert.ToInt32(item["num"]);
                                    res.Add(type);
                                }
                                string sqlCar = $@"select b.business_unit,count(*)num from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
										join qskj.B_Taxi d on a.vehicleNo=d.license_plate 
										where c.threeClassId='{1}' and c.busOrCar='c'  and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{endTime.ToString("yyyy-MM-dd HH:mm:ss")}' and dep='{dep.dep}'
                                        group by b.business_unit;";
                                DataTable resCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                                foreach (DataRow item in resCar.Rows)
                                {
                                    AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                    type.time = startTime.ToString("yyyy-MM-dd");
                                    type.company = item["business_unit"] == DBNull.Value ? "" : item["business_unit"].ToString();
                                    type.num = Convert.ToInt32(item["num"]);
                                    res.Add(type);
                                }
                            }
                            res = res.GroupBy(x => x.company).Select(p => new AlarmVehicleCompanyByToday { time = startTime.ToString("yyyy-MM-dd"), company = p.Key, num = p.Sum(x => x.num) }).ToList();
                            res = res.OrderByDescending(p => p.num).ToList();
                            foreach (var item in res)
                            {
                                string sqlIn = $@"insert into qskj.D_AlarmCompanyData(companyName,dataNum,typeId,dataType,dayDate)values('{item.company}',{item.num},1,'天','{item.time}')";
                                int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "公司当日报警总数数据汇总1写入异常：" + JsonConvert.SerializeObject(e));
                    }
                    try
                    {
                        List<AlarmVehicleCompanyByToday> res = new List<AlarmVehicleCompanyByToday>();
                        {
                            foreach (var dep in depList)
                            {
                                string sqlBus = $@"select b.business_unit,count(*)num from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
										join qskj.B_Bus d on a.vehicleNo=d.license_plate 
										where c.threeClassId='2' and c.busOrCar='b'  and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{endTime.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.business_unit;";
                                DataTable resBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                                foreach (DataRow item in resBus.Rows)
                                {
                                    AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                    type.time = startTime.ToString("yyyy-MM-dd");
                                    type.company = item["business_unit"] == DBNull.Value ? "" : item["business_unit"].ToString();
                                    type.num = Convert.ToInt32(item["num"]);
                                    res.Add(type);
                                }
                                string sqlCar = $@"select b.business_unit,count(*)num from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
										join qskj.B_Taxi d on a.vehicleNo=d.license_plate 
										where c.threeClassId='2' and c.busOrCar='c'  and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{endTime.ToString("yyyy-MM-dd HH:mm:ss")}' and dep='{dep.dep}'
                                        group by b.business_unit;";
                                DataTable resCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                                foreach (DataRow item in resCar.Rows)
                                {
                                    AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                    type.time = startTime.ToString("yyyy-MM-dd");
                                    type.company = item["business_unit"] == DBNull.Value ? "" : item["business_unit"].ToString();
                                    type.num = Convert.ToInt32(item["num"]);
                                    res.Add(type);
                                }
                            }
                            res = res.GroupBy(x => x.company).Select(p => new AlarmVehicleCompanyByToday { time = startTime.ToString("yyyy-MM-dd"), company = p.Key, num = p.Sum(x => x.num) }).ToList();
                            res = res.OrderByDescending(p => p.num).ToList();
                            foreach (var item in res)
                            {
                                string sqlIn = $@"insert into qskj.D_AlarmCompanyData(companyName,dataNum,typeId,dataType,dayDate)values('{item.company}',{item.num},2,'天','{item.time}')";
                                int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "公司当日报警总数数据汇总2写入异常：" + JsonConvert.SerializeObject(e));
                    }
                    try
                    {
                        List<AlarmVehicleCompanyByToday> res = new List<AlarmVehicleCompanyByToday>();
                        {
                            foreach (var dep in depList)
                            {
                                string sqlBus = $@"select b.business_unit,count(*)num from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
										join qskj.B_Bus d on a.vehicleNo=d.license_plate 
										where c.threeClassId='3' and c.busOrCar='b'  and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{endTime.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.business_unit;";
                                DataTable resBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                                foreach (DataRow item in resBus.Rows)
                                {
                                    AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                    type.time = startTime.ToString("yyyy-MM-dd");
                                    type.company = item["business_unit"] == DBNull.Value ? "" : item["business_unit"].ToString();
                                    type.num = Convert.ToInt32(item["num"]);
                                    res.Add(type);
                                }
                                string sqlCar = $@"select b.business_unit,count(*)num from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
										join qskj.B_Taxi d on a.vehicleNo=d.license_plate 
										where c.threeClassId='3' and c.busOrCar='c'  and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{endTime.ToString("yyyy-MM-dd HH:mm:ss")}' and dep='{dep.dep}'
                                        group by b.business_unit;";
                                DataTable resCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                                foreach (DataRow item in resCar.Rows)
                                {
                                    AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                    type.time = startTime.ToString("yyyy-MM-dd");
                                    type.company = item["business_unit"] == DBNull.Value ? "" : item["business_unit"].ToString();
                                    type.num = Convert.ToInt32(item["num"]);
                                    res.Add(type);
                                }
                            }
                            res = res.GroupBy(x => x.company).Select(p => new AlarmVehicleCompanyByToday { time = startTime.ToString("yyyy-MM-dd"), company = p.Key, num = p.Sum(x => x.num) }).ToList();
                            res = res.OrderByDescending(p => p.num).ToList();
                            foreach (var item in res)
                            {
                                string sqlIn = $@"insert into qskj.D_AlarmCompanyData(companyName,dataNum,typeId,dataType,dayDate)values('{item.company}',{item.num},3,'天','{item.time}')";
                                int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            }
                        }
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "公司当日报警总数数据汇总3写入异常：" + JsonConvert.SerializeObject(e));
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "公司当日报警总数数据汇总写入异常：" + JsonConvert.SerializeObject(e));
            }
        }
        /// <summary>
        /// 2.11 报警排名最高类型TOP10返回车辆报警排名最高类型TOP10
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmTypeVehicleTopTen(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间
            //for (DateTime time = startTime; time <= endTime; time = time.AddHours(1))
            {
                try
                {
                    List<AlarmTypeVehicleTopTen> res = new List<AlarmTypeVehicleTopTen>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        //string sql1 = $@"select  count(*)num,c.qskjName from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} as a
                        //                join qskj.B_Bus b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.busorcar='b' and c.threeClassId='1' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                        //                group by c.qskjName;";
                        string sql1 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='b' and c.threeClassId='1' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                        group by c.qskjName;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "bus";
                            res.Add(type);
                        }
                        //string sql2 = $@"select  count(*)num,c.qskjName from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} as a
                        //                join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.busorcar='c' and c.threeClassId='1' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                        //                group by c.qskjName;";
                        string sql2 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='c' and c.threeClassId='1' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                        group by c.qskjName;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "car";
                            res.Add(type);
                        }
                        res = res.GroupBy(x => new { x.warningType, x.busOrCar }).Select(p => new AlarmTypeVehicleTopTen { warningType = p.Key.warningType, busOrCar = p.Key.busOrCar, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeData(typeName,dataNum,typeId,dataType,dayDate,busOrCar) values('{item.warningType}',{item.num},1,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.busOrCar}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高类型1写入结果：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.11 报警排名最高类型TOP10返回车辆报警排名最高类型TOP10---1写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleTopTen> res = new List<AlarmTypeVehicleTopTen>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        //string sql1 = $@"select  count(*)num,c.qskjName from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} as a
                        //                join qskj.B_Bus b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.busorcar='b' and c.threeClassId='2' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                        //                group by c.qskjName;";
                        string sql1 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='b' and c.threeClassId='2' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                        group by c.qskjName;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "bus";
                            res.Add(type);
                        }
                        //string sql2 = $@"select  count(*)num,c.qskjName from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} as a
                        //                join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.busorcar='c' and c.threeClassId='2' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                        //                group by c.qskjName;";
                        string sql2 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='c' and c.threeClassId='2' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                        group by c.qskjName;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "car";
                            res.Add(type);
                        }
                        res = res.GroupBy(x => new { x.warningType, x.busOrCar }).Select(p => new AlarmTypeVehicleTopTen { warningType = p.Key.warningType, busOrCar = p.Key.busOrCar, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeData(typeName,dataNum,typeId,dataType,dayDate,busOrCar) values('{item.warningType}',{item.num},2,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.busOrCar}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高类型2写入结果：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.11 报警排名最高类型TOP10返回车辆报警排名最高类型TOP10---2写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleTopTen> res = new List<AlarmTypeVehicleTopTen>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        //string sql1 = $@"select  count(*)num,c.qskjName from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} as a
                        //                join qskj.B_Bus b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.busorcar='b' and c.threeClassId='3' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                        //                group by c.qskjName;";
                        string sql1 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='b' and c.threeClassId='3' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                        group by c.qskjName;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "bus";
                            res.Add(type);
                        }
                        //string sql2 = $@"select  count(*)num,c.qskjName from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} as a
                        //                join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.busorcar='c' and c.threeClassId='3' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                        //                group by c.qskjName;";
                        string sql2 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='c' and c.threeClassId='3' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}'and a.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                        group by c.qskjName;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "car";
                            res.Add(type);
                        }
                        res = res.GroupBy(x => new { x.warningType, x.busOrCar }).Select(p => new AlarmTypeVehicleTopTen { warningType = p.Key.warningType, busOrCar = p.Key.busOrCar, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeData(typeName,dataNum,typeId,dataType,dayDate,busOrCar) values('{item.warningType}',{item.num},3,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.busOrCar}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高类型3写入结果：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.11 报警排名最高类型TOP10返回车辆报警排名最高类型TOP10---3写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        /// <summary>
        /// 2.111 车队报警排名最高类型TOP10返回车辆报警排名最高类型TOP10
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmTypeVehicleTopTenForFiliale(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间
            string dtStr = startTime.ToString("yyyyMMdd");
            List<string> depList = new List<string>();
            {
                string sqlDep = $@"select dep from qskjrecord.B_BusZT{dtStr} group by dep;";
                DataTable dtDep = MySqlHelper.GetDataTable(CommandType.Text, sqlDep);
                foreach (DataRow item in dtDep.Rows)
                {
                    depList.Add(item["dep"].ToString());
                }
            }
            foreach (var dep in depList)
            {
                try
                {
                    List<AlarmTypeVehicleTopTen> res = new List<AlarmTypeVehicleTopTen>();
                    {
                        string sql1 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                            join (
                                                    select devGbId,alarmId,max(a.id)id from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep}'  group by devGbId,alarmId
                                                 ) ab on aa.id=ab.id) as a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='b' and c.threeClassId='1' 
                                        group by c.qskjName;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "bus";
                            res.Add(type);
                        }
                        string sql2 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                            join (
                                                    select devGbId,alarmId,max(a.id)id from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep}'  group by devGbId,alarmId
                                                 ) ab on aa.id=ab.id) as a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='c' and c.threeClassId='1' 
                                        group by c.qskjName;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "car";
                            res.Add(type);
                        }
                        res = res.GroupBy(x => new { x.warningType, x.busOrCar }).Select(p => new AlarmTypeVehicleTopTen { warningType = p.Key.warningType, busOrCar = p.Key.busOrCar, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeDataFiliale(typeName,dataNum,typeId,dataType,dayDate,busOrCar,filialename) values('{item.warningType}',{item.num},1,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.busOrCar}','{dep}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高类型1写入结果：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.111 报警排名最高类型TOP10返回车辆报警排名最高类型TOP10---1写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleTopTen> res = new List<AlarmTypeVehicleTopTen>();
                    {
                        string sql1 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                            join (
                                                    select devGbId,alarmId,max(a.id)id from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep}'  group by devGbId,alarmId
                                                 ) ab on aa.id=ab.id) as a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='b' and c.threeClassId='2' 
                                        group by c.qskjName;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "bus";
                            res.Add(type);
                        }
                        string sql2 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                            join (
                                                    select devGbId,alarmId,max(a.id)id from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep}'  group by devGbId,alarmId
                                                 ) ab on aa.id=ab.id) as a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='c' and c.threeClassId='2' 
                                        group by c.qskjName;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "car";
                            res.Add(type);
                        }
                        res = res.GroupBy(x => new { x.warningType, x.busOrCar }).Select(p => new AlarmTypeVehicleTopTen { warningType = p.Key.warningType, busOrCar = p.Key.busOrCar, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeDataFiliale(typeName,dataNum,typeId,dataType,dayDate,busOrCar,filialename) values('{item.warningType}',{item.num},2,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.busOrCar}','{dep}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高类型2写入结果：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.111 报警排名最高类型TOP10返回车辆报警排名最高类型TOP10---2写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleTopTen> res = new List<AlarmTypeVehicleTopTen>();
                    {
                        string sql1 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                            join (
                                                    select devGbId,alarmId,max(a.id)id from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep}'  group by devGbId,alarmId
                                                 ) ab on aa.id=ab.id) as a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='b' and c.threeClassId='3' 
                                        group by c.qskjName;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "bus";
                            res.Add(type);
                        }
                        string sql2 = $@"select  count(*)num,c.qskjName from (
                                            select aa.vehicleNo,aa.alarmType from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                            join (
                                                    select devGbId,alarmId,max(a.id)id from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                                    join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep}'  group by devGbId,alarmId
                                                 ) ab on aa.id=ab.id) as a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.busorcar='c' and c.threeClassId='3' 
                                        group by c.qskjName;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleTopTen type = new AlarmTypeVehicleTopTen();
                            type.warningType = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            type.busOrCar = "car";
                            res.Add(type);
                        }
                        res = res.GroupBy(x => new { x.warningType, x.busOrCar }).Select(p => new AlarmTypeVehicleTopTen { warningType = p.Key.warningType, busOrCar = p.Key.busOrCar, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeDataFiliale(typeName,dataNum,typeId,dataType,dayDate,busOrCar,filialename) values('{item.warningType}',{item.num},3,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}','{item.busOrCar}','{dep}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高类型3写入结果：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.111 报警排名最高类型TOP10返回车辆报警排名最高类型TOP10---3写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        /// <summary>
        /// 2.12 报警排名最高车队TOP10返回车辆报警排名最高车队TOP10
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmTypeVehicleFilialeTopTen(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间
            try
            {
                string dtStr = DateTime.Now.ToString("yyyyMMdd");
                List<DepAnalyze> depList = new List<DepAnalyze>();
                {
                    string sqlDep = $@"select car_assets_name_name,dep,count(*)num  from qskjrecord.B_BusZT{dtStr} group by car_assets_name_name,dep;";
                    DataTable dtDep = MySqlHelper.GetDataTable(CommandType.Text, sqlDep);
                    foreach (DataRow item in dtDep.Rows)
                    {
                        DepAnalyze depAnalyze = new DepAnalyze();
                        depAnalyze.company = item["car_assets_name_name"].ToString();
                        depAnalyze.dep = item["dep"].ToString();
                        depAnalyze.num = Convert.ToInt32(item["num"]);
                        depList.Add(depAnalyze);
                    }
                }
                try
                {
                    List<AlarmTypeVehicleFilialeTopTen> res = new List<AlarmTypeVehicleFilialeTopTen>();
                    {
                        foreach (var dep in depList)
                        {
                            string sqlBus = $@"select  count(*)num,b.dep name from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId 
                                        join qskj.B_Bus d on a.vehicleNo=d.license_plate
                                        where c.threeClassId='1' and c.busOrCar='b' and a.createtime>='{dts.ToString("yyyy-MM-dd")}'and a.createtime<'{DateTime.Now.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.dep;";
                            DataTable resBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                            foreach (DataRow item in resBus.Rows)
                            {
                                AlarmTypeVehicleFilialeTopTen type = new AlarmTypeVehicleFilialeTopTen();
                                type.name = item["name"] == DBNull.Value ? "" : item["name"].ToString();
                                type.num = Convert.ToInt32(item["num"]);
                                res.Add(type);
                            }
                            string sqlCar = $@"select  count(*)num,b.dep name from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId 
                                        join qskj.B_Taxi d on a.vehicleNo=d.license_plate
                                        where c.threeClassId='1' and c.busOrCar='c' and a.createtime>='{dts.ToString("yyyy-MM-dd")}'and a.createtime<'{DateTime.Now.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.dep;";
                            DataTable resCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                            foreach (DataRow item in resCar.Rows)
                            {
                                AlarmTypeVehicleFilialeTopTen type = new AlarmTypeVehicleFilialeTopTen();
                                type.name = item["name"] == DBNull.Value ? "" : item["name"].ToString();
                                type.num = Convert.ToInt32(item["num"]);
                                res.Add(type);
                            }
                        }
                        res = res.GroupBy(x => x.name).Select(p => new AlarmTypeVehicleFilialeTopTen { name = p.Key, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeVehicleFilialeData(filialeName,dataNum,typeId,dataType,dayDate) values('{item.name}',{item.num},1,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高车队写入结果1失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.12 报警排名最高车队TOP10返回车辆报警排名最高车队TOP10---1写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleFilialeTopTen> res = new List<AlarmTypeVehicleFilialeTopTen>();
                    {
                        foreach (var dep in depList)
                        {
                            string sqlBus = $@"select  count(*)num,b.dep name from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId 
                                        join qskj.B_Bus d on a.vehicleNo=d.license_plate
                                        where c.threeClassId='2' and c.busOrCar='b' and a.createtime>='{dts.ToString("yyyy-MM-dd")}'and a.createtime<'{DateTime.Now.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.dep;";
                            DataTable resBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                            foreach (DataRow item in resBus.Rows)
                            {
                                AlarmTypeVehicleFilialeTopTen type = new AlarmTypeVehicleFilialeTopTen();
                                type.name = item["name"] == DBNull.Value ? "" : item["name"].ToString();
                                type.num = Convert.ToInt32(item["num"]);
                                res.Add(type);
                            }
                            string sqlCar = $@"select  count(*)num,b.dep name from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId 
                                        join qskj.B_Taxi d on a.vehicleNo=d.license_plate
                                        where c.threeClassId='2' and c.busOrCar='c' and a.createtime>='{dts.ToString("yyyy-MM-dd")}'and a.createtime<'{DateTime.Now.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.dep;";
                            DataTable resCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                            foreach (DataRow item in resCar.Rows)
                            {
                                AlarmTypeVehicleFilialeTopTen type = new AlarmTypeVehicleFilialeTopTen();
                                type.name = item["name"] == DBNull.Value ? "" : item["name"].ToString();
                                type.num = Convert.ToInt32(item["num"]);
                                res.Add(type);
                            }
                        }
                        res = res.GroupBy(x => x.name).Select(p => new AlarmTypeVehicleFilialeTopTen { name = p.Key, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeVehicleFilialeData(filialeName,dataNum,typeId,dataType,dayDate) values('{item.name}',{item.num},2,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高车队写入结果2失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.12 报警排名最高车队TOP10返回车辆报警排名最高车队TOP10---2写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleFilialeTopTen> res = new List<AlarmTypeVehicleFilialeTopTen>();
                    {
                        foreach (var dep in depList)
                        {
                            string sqlBus = $@"select  count(*)num,b.dep name from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId 
                                        join qskj.B_Bus d on a.vehicleNo=d.license_plate
                                        where c.threeClassId='3' and c.busOrCar='b' and a.createtime>='{dts.ToString("yyyy-MM-dd")}'and a.createtime<'{DateTime.Now.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.dep;";
                            DataTable resBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                            foreach (DataRow item in resBus.Rows)
                            {
                                AlarmTypeVehicleFilialeTopTen type = new AlarmTypeVehicleFilialeTopTen();
                                type.name = item["name"] == DBNull.Value ? "" : item["name"].ToString();
                                type.num = Convert.ToInt32(item["num"]);
                                res.Add(type);
                            }
                            string sqlCar = $@"select  count(*)num,b.dep name from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) as a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers 
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId 
                                        join qskj.B_Taxi d on a.vehicleNo=d.license_plate
                                        where c.threeClassId='3' and c.busOrCar='c' and a.createtime>='{dts.ToString("yyyy-MM-dd")}'and a.createtime<'{DateTime.Now.ToString("yyyy-MM-dd")}' and dep='{dep.dep}'
                                        group by b.dep;";
                            DataTable resCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                            foreach (DataRow item in resCar.Rows)
                            {
                                AlarmTypeVehicleFilialeTopTen type = new AlarmTypeVehicleFilialeTopTen();
                                type.name = item["name"] == DBNull.Value ? "" : item["name"].ToString();
                                type.num = Convert.ToInt32(item["num"]);
                                res.Add(type);
                            }
                        }
                        res = res.GroupBy(x => x.name).Select(p => new AlarmTypeVehicleFilialeTopTen { name = p.Key, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeVehicleFilialeData(filialeName,dataNum,typeId,dataType,dayDate) values('{item.name}',{item.num},3,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "报警排名最高车队写入结果3失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.12 报警排名最高车队TOP10返回车辆报警排名最高车队TOP10---3写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.12 报警排名最高车队TOP10返回车辆报警排名最高车队TOP10---写入异常：" + JsonConvert.SerializeObject(e));
            }
        }
        /// <summary>
        /// 2.13 当日排名最高报警车辆TOP10返回车辆当日排名最高报警车辆TOP10
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmTypeVehicleBcTopTen(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间
            //for (DateTime time = startTime; time <= endTime; time = time.AddHours(1))
            {
                try
                {
                    List<AlarmTypeVehicleBcTopTen> res = new List<AlarmTypeVehicleBcTopTen>();
                    {
                        //string sql1 = $@"select  count(*)num,vehicleNo from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                        //                join qskj.B_Bus b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.threeClassId='1' and c.busOrCar='b' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                        //                group by vehicleNo;";
                        string sql1 = $@"select  count(*)num,vehicleNo from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.threeClassId='1' and c.busOrCar='b' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                                        group by vehicleNo;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleBcTopTen type = new AlarmTypeVehicleBcTopTen();
                            type.busCode = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            res.Add(type);
                        }
                        //string sql2 = $@"select  count(*)num,vehicleNo from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                        //                join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.threeClassId='1' and c.busOrCar='c' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                        //                group by vehicleNo;";
                        string sql2 = $@"select  count(*)num,vehicleNo from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.threeClassId='1' and c.busOrCar='c' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                                        group by vehicleNo;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleBcTopTen type = new AlarmTypeVehicleBcTopTen();
                            type.busCode = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            res.Add(type);
                        }
                        res = res.GroupBy(x => x.busCode).Select(p => new AlarmTypeVehicleBcTopTen { busCode = p.Key, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeVehicleBc(busCode,dataNum,typeId,dataType,dayDate) values('{item.busCode}',{item.num},1,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日排名最高报警车辆写入结果1失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.13 当日排名最高报警车辆TOP10返回车辆当日排名最高报警车辆TOP10---1写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleBcTopTen> res = new List<AlarmTypeVehicleBcTopTen>();
                    {
                        //string sql1 = $@"select  count(*)num,vehicleNo from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                        //                join qskj.B_Bus b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.threeClassId='2' and c.busOrCar='b' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                        //                group by vehicleNo;";
                        string sql1 = $@"select  count(*)num,vehicleNo from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.threeClassId='2' and c.busOrCar='b' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                                        group by vehicleNo;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleBcTopTen type = new AlarmTypeVehicleBcTopTen();
                            type.busCode = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            res.Add(type);
                        }
                        //string sql2 = $@"select  count(*)num,vehicleNo from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                        //                join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.threeClassId='2' and c.busOrCar='c' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                        //                group by vehicleNo;";
                        string sql2 = $@"select  count(*)num,vehicleNo from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.threeClassId='2' and c.busOrCar='c' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                                        group by vehicleNo;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleBcTopTen type = new AlarmTypeVehicleBcTopTen();
                            type.busCode = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            res.Add(type);
                        }
                        res = res.GroupBy(x => x.busCode).Select(p => new AlarmTypeVehicleBcTopTen { busCode = p.Key, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeVehicleBc(busCode,dataNum,typeId,dataType,dayDate) values('{item.busCode}',{item.num},2,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日排名最高报警车辆写入结果2失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.13 当日排名最高报警车辆TOP10返回车辆当日排名最高报警车辆TOP10---2写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmTypeVehicleBcTopTen> res = new List<AlarmTypeVehicleBcTopTen>();
                    {
                        //string sql1 = $@"select  count(*)num,vehicleNo from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                        //                join qskj.B_Bus b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.threeClassId='3' and c.busOrCar='b' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                        //                group by vehicleNo;";
                        string sql1 = $@"select  count(*)num,vehicleNo from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) a
                                        join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.threeClassId='3' and c.busOrCar='b' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                                        group by vehicleNo;";
                        DataTable res1 = MySqlHelper.GetDataTable(CommandType.Text, sql1);
                        foreach (DataRow item in res1.Rows)
                        {
                            AlarmTypeVehicleBcTopTen type = new AlarmTypeVehicleBcTopTen();
                            type.busCode = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            res.Add(type);
                        }
                        //string sql2 = $@"select  count(*)num,vehicleNo from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                        //                join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                        //                join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                where c.threeClassId='3' and c.busOrCar='c' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                        //                group by vehicleNo;";
                        string sql2 = $@"select  count(*)num,vehicleNo from (
                                            select aa.vehicleNo,aa.alarmType,aa.createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} aa
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) ab on aa.devGbId=ab.devGbId and aa.alarmId=ab.alarmId and aa.createtime=ab.createtime) a
                                        join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                        join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                                        where c.threeClassId='3' and c.busOrCar='c' and a.createtime>='{startTime.ToString("yyyy-MM-dd")}' and a.createtime<'{dte.ToString("yyyy-MM-dd")}'
                                        group by vehicleNo;";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmTypeVehicleBcTopTen type = new AlarmTypeVehicleBcTopTen();
                            type.busCode = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            type.num = Convert.ToInt32(item["num"]);
                            res.Add(type);
                        }
                        res = res.GroupBy(x => x.busCode).Select(p => new AlarmTypeVehicleBcTopTen { busCode = p.Key, num = p.Sum(x => x.num) }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmTypeVehicleBc(busCode,dataNum,typeId,dataType,dayDate) values('{item.busCode}',{item.num},3,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日排名最高报警车辆写入结果3失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.13 当日排名最高报警车辆TOP10返回车辆当日排名最高报警车辆TOP10---3写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        /// <summary>
        /// 2.14 当日排名最高报警驾驶员TOP200
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmDrverData(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间
            //for (DateTime time = startTime; time <= endTime; time = time.AddHours(1))
            {
                try
                {
                    List<AlarmDriver> res = new List<AlarmDriver>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        string sqlBus = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime) aa
                                join qskj.B_Bus ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.threeClassId='1' and ac.busOrCar='b' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                        foreach (DataRow item in dtBus.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        string sqlCar = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Taxi ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.threeClassId='1' and ac.busOrCar='c' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                        foreach (DataRow item in dtCar.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmDrverData(driverName,driverId,driverWId,vehicleNo,dep,accountname,shortname,dataNum,typeId,dataType,dayDate) values('{item.driverName}','{item.driverId}','{item.driverWId}','{item.vehicleNo}','{item.filiale}','{item.company}','{item.shortname}',{item.num},1,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日排名最高报警驾驶员写入结果1失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.14 当日排名最高报警驾驶员---1写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmDriver> res = new List<AlarmDriver>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        string sqlBus = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Bus ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.threeClassId='2' and ac.busOrCar='b' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                        foreach (DataRow item in dtBus.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        string sqlCar = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Taxi ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.threeClassId='2' and ac.busOrCar='c' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                        foreach (DataRow item in dtCar.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmDrverData(driverName,driverId,driverWId,vehicleNo,dep,accountname,shortname,dataNum,typeId,dataType,dayDate) 
                                                values('{item.driverName}','{item.driverId}','{item.driverWId}','{item.vehicleNo}','{item.filiale}','{item.company}','{item.shortname}',{item.num},2,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日排名最高报警驾驶员写入结果2失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.14 当日排名最高报警驾驶员---2写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    List<AlarmDriver> res = new List<AlarmDriver>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        string sqlBus = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Bus ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.threeClassId='3' and ac.busOrCar='b' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                        foreach (DataRow item in dtBus.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        string sqlCar = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Taxi ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.threeClassId='3' and ac.busOrCar='c' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                        foreach (DataRow item in dtCar.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmDrverData(driverName,driverId,driverWId,vehicleNo,dep,accountname,shortname,dataNum,typeId,dataType,dayDate) 
                                            values('{item.driverName}','{item.driverId}','{item.driverWId}','{item.vehicleNo}','{item.filiale}','{item.company}','{item.shortname}',{item.num},3,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日排名最高报警驾驶员写入结果3失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.14 当日排名最高报警驾驶员---3写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    //驾驶员报警分类明细统计
                    List<AlarmDriver> res = new List<AlarmDriver>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        string sqlBus = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname,a.qskjId,a.qskjName from (
                                select driverName,driverId,driverWId,vehicleNo,ac.qskjId,ac.qskjName,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Bus ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.busOrCar='b' 
                                group by driverName,driverId,driverWId,vehicleNo,ac.qskjId,ac.qskjName
                                order by num desc )a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc ;";
                        DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                        foreach (DataRow item in dtBus.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            ad.qskjId = item["qskjId"] == DBNull.Value ? 0 : Convert.ToInt32(item["qskjId"]);
                            ad.qskjName = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            res.Add(ad);
                        }
                        string sqlCar = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname,a.qskjId,a.qskjName from (
                                select driverName,driverId,driverWId,vehicleNo,ac.qskjId,ac.qskjName,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Taxi ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.busOrCar='c' 
                                group by driverName,driverId,driverWId,vehicleNo,ac.qskjId,ac.qskjName
                                order by num desc )a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc ;";
                        DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                        foreach (DataRow item in dtCar.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            ad.qskjId = item["qskjId"] == DBNull.Value ? 0 : Convert.ToInt32(item["qskjId"]);
                            ad.qskjName = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            res.Add(ad);
                        }
                        foreach (var item in res)
                        {
                            if (string.IsNullOrEmpty(item.driverName))
                            {
                                continue;
                            }
                            string sqlIn = $@"insert into qskj.D_AlarmDrverDataDetail(driverName,driverId,driverWId,vehicleNo,dep,accountname,shortname,dataNum,typeId,dataType,dayDate,qskjId,qskjName) 
                                            values('{item.driverName}','{item.driverId}','{item.driverWId}','{item.vehicleNo}','{item.filiale}','{item.company}','{item.shortname}',{item.num},3,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}',{item.qskjId},'{item.qskjName}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日排名最高报警驾驶员报警类型写入结果4失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.14 当日排名最高报警驾驶员---3写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        /// <summary>
        /// 2.14 2.141 当日排名最高报警驾驶员(类型为1抽烟，9超速，3疲劳)TOP200
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmDrverDataCcp(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间
            //for (DateTime time = startTime; time <= endTime; time = time.AddHours(1))
            {
                //驾驶员1抽烟报警数据
                try
                {
                    List<AlarmDriver> res = new List<AlarmDriver>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        string sqlBus = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime) aa
                                join qskj.B_Bus ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.qskjId=1 and ac.busOrCar='b' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                        foreach (DataRow item in dtBus.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        string sqlCar = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Taxi ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.qskjId=1 and ac.busOrCar='c' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                        foreach (DataRow item in dtCar.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmDrverDataCcp(driverName,driverId,driverWId,vehicleNo,dep,accountname,shortname,dataNum,typeId,dataType,dayDate) values('{item.driverName}','{item.driverId}','{item.driverWId}','{item.vehicleNo}','{item.filiale}','{item.company}','{item.shortname}',{item.num},1,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日1抽烟排名最高报警驾驶员写入结果1失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.141 当日1抽烟排名最高报警驾驶员---1写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    //驾驶员9超速报警数据
                    List<AlarmDriver> res = new List<AlarmDriver>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        string sqlBus = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Bus ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.qskjId=9 and ac.busOrCar='b' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                        foreach (DataRow item in dtBus.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        string sqlCar = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Taxi ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.qskjId=9 and ac.busOrCar='c' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                        foreach (DataRow item in dtCar.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmDrverDataCcp(driverName,driverId,driverWId,vehicleNo,dep,accountname,shortname,dataNum,typeId,dataType,dayDate) 
                                                values('{item.driverName}','{item.driverId}','{item.driverWId}','{item.vehicleNo}','{item.filiale}','{item.company}','{item.shortname}',{item.num},9,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日9超速排名最高报警驾驶员写入结果2失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.141 当日9超速排名最高报警驾驶员---2写入异常：" + JsonConvert.SerializeObject(e));
                }
                try
                {
                    //驾驶员3疲劳报警数据
                    List<AlarmDriver> res = new List<AlarmDriver>();
                    {
                        string dtStr = DateTime.Now.ToString("yyyyMMdd");
                        string sqlBus = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Bus ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.qskjId=3 and ac.busOrCar='b' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                        foreach (DataRow item in dtBus.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        string sqlCar = $@"select driverName,driverId,driverWId,vehicleNo,num,b.dep,b.car_assets_name_name,b.shortname from (
                                select driverName,driverId,driverWId,vehicleNo,count(1)num from (
                                            select a.vehicleNo,a.alarmType,a.createtime,driverName,driverId,driverWId from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} a
                                            join (
                                                    select devGbId,alarmId,max(createtime)createtime from qskjrecord.B_Vehicle_Alarm{startTime.ToString("yyyyMMdd")} group by devGbId,alarmId
                                                 ) b on a.devGbId=b.devGbId and a.alarmId=b.alarmId and a.createtime=b.createtime)  aa
                                join qskj.B_Taxi ab on aa.vehicleNo=ab.license_plate 
                                join qskj.R_Alarm_Type ac on aa.alarmType=ac.typeId
                                where ac.qskjId=3 and ac.busOrCar='c' and aa.createtime>='{startTime.ToString("yyyy-MM-dd")}'and aa.createtime<'{endTime.ToString("yyyy-MM-dd")}'
                                group by driverName,driverId,driverWId,vehicleNo
                                order by num desc
                                limit 90)a
                                join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                                 order by num desc limit 100;";
                        DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                        foreach (DataRow item in dtCar.Rows)
                        {
                            AlarmDriver ad = new AlarmDriver();
                            ad.driverName = item["driverName"] == DBNull.Value ? "" : item["driverName"].ToString();
                            ad.driverId = item["driverId"] == DBNull.Value ? "" : item["driverId"].ToString();
                            ad.driverWId = item["driverWId"] == DBNull.Value ? "" : item["driverWId"].ToString();
                            ad.vehicleNo = item["vehicleNo"] == DBNull.Value ? "" : item["vehicleNo"].ToString();
                            ad.filiale = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                            ad.company = item["car_assets_name_name"] == DBNull.Value ? "" : item["car_assets_name_name"].ToString();
                            ad.shortname = item["shortname"] == DBNull.Value ? "" : item["shortname"].ToString();
                            ad.num = item["num"] == DBNull.Value ? 0 : Convert.ToInt32(item["num"]);
                            res.Add(ad);
                        }
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmDrverDataCcp(driverName,driverId,driverWId,vehicleNo,dep,accountname,shortname,dataNum,typeId,dataType,dayDate) 
                                            values('{item.driverName}','{item.driverId}','{item.driverWId}','{item.vehicleNo}','{item.filiale}','{item.company}','{item.shortname}',{item.num},3,'天','{startTime.ToString("yyyy-MM-dd HH:mm:ss")}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                            if (resIn == 0)
                            {
                                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日3疲劳排名最高报警驾驶员写入结果3失败：" + res);
                            }
                        }
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.141 当日3疲劳排名最高报警驾驶员---3写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        /// <summary>
        /// 2.9 近10天四公司车辆报警柱状图详情
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmVehicleCompanyByTenToday(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间 
            {
                try
                {
                    List<AlarmVehicleCompanyByTenToday> res = new List<AlarmVehicleCompanyByTenToday>();
                    string dtStr = startTime.ToString("yyyyMMdd");
                    //string sql2 = $@"select b.qskjName,count(*)num from qskjrecord.B_Vehicle_Alarm{dtStr} as a
                    //                    join qskj.R_Alarm_Type b on a.alarmType=b.typeId
                    //                    where ((busOrCar='b' and platform='ZAKJ') or (busOrCar='c' and platform='RUIMING')) and (b.qskjId='1' or b.qskjId='2' or b.qskjId='3' or b.qskjId='9')
                    //                    group by b.qskjName; ";
                    string sql2 = $@"select b.qskjName,count(*)num from (select devGbId,alarmId,alarmType,platform from qskjrecord.B_Vehicle_Alarm{dtStr} group by devGbId,alarmId,alarmType,platform) as a
                                        join qskj.R_Alarm_Type b on a.alarmType=b.typeId
                                        where ((busOrCar='b' and platform='ZAKJ') or (busOrCar='c' and (platform='RUIMING' or platform='zl'))) and (b.qskjId='1' or b.qskjId='2' or b.qskjId='3' or b.qskjId='9')
                                        group by b.qskjName; ";
                    DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                    foreach (DataRow item in res2.Rows)
                    {
                        AlarmVehicleCompanyByTenToday type = new AlarmVehicleCompanyByTenToday();
                        type.time = startTime.ToString("yyyy-MM-dd");
                        type.company = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                        type.num = Convert.ToInt32(item["num"]);
                        res.Add(type);
                        string sqlIns = $@"insert into qskjrecord.D_CompanyData(companyname,num,datetime)values('{type.company}',{type.num},'{startTime.ToString("yyyy-MM-dd")}')";
                        MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIns);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.9 近10天四公司车辆报警柱状图详情---1写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        /// <summary>
        /// 2.91 近10天车队车辆四种类型报警柱状图详情
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateAlarmVehicleCompanyByTenTodayForFili(DateTime dts, DateTime dte)
        {
            DateTime startTime = dts;//前一天0点
            DateTime endTime = dte;//当前时间 
            {
                try
                {
                    List<AlarmVehicleCompanyByTenToday> res = new List<AlarmVehicleCompanyByTenToday>();
                    string dtStr = startTime.ToString("yyyyMMdd");
                    List<DepAnalyze> depList = new List<DepAnalyze>();
                    {
                        string sqlDep = $@"select car_assets_name_name,dep,count(*)num  from qskjrecord.B_BusZT{dtStr} group by car_assets_name_name,dep;";
                        DataTable dtDep = MySqlHelper.GetDataTable(CommandType.Text, sqlDep);
                        foreach (DataRow item in dtDep.Rows)
                        {
                            DepAnalyze depAnalyze = new DepAnalyze();
                            depAnalyze.company = item["car_assets_name_name"].ToString();
                            depAnalyze.dep = item["dep"].ToString();
                            depAnalyze.num = Convert.ToInt32(item["num"]);
                            depList.Add(depAnalyze);
                        }
                    }
                    List<DepAnalyze> depList1 = new List<DepAnalyze>();//报警车辆数
                    foreach (var dep in depList)
                    {
                        string sqlDep = $@" select qskjName,qskjId,count(*)num from (
                                        select b.qskjName,b.qskjId,a.devGbId  from (select devGbId,alarmId,alarmType,platform from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep.dep}' group by devGbId,alarmId,alarmType,platform) as a
                                        join qskj.R_Alarm_Type b on a.alarmType=b.typeId
                                        where ((busOrCar='b' and platform='ZAKJ') or (busOrCar='c' and (platform='RUIMING' or platform='zl'))) 
                                        group by b.qskjName,b.qskjId,a.devGbId) d group by  qskjName,qskjId;";
                        DataTable dtDep = MySqlHelper.GetDataTable(CommandType.Text, sqlDep);
                        foreach (DataRow item in dtDep.Rows)
                        {
                            DepAnalyze depAnalyze = new DepAnalyze();
                            depAnalyze.company = item["qskjName"].ToString();
                            depAnalyze.dep = dep.dep;
                            depAnalyze.num = Convert.ToInt32(item["num"]);
                            depList1.Add(depAnalyze);
                        }
                    }
                    foreach (var dep in depList)
                    {
                        string sql2 = $@"select b.qskjName,b.qskjId,count(*)num from (select devGbId,alarmId,alarmType,platform from qskjrecord.B_Vehicle_Alarm{dtStr} a
                                        join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers where dep='{dep.dep}' group by devGbId,alarmId,alarmType,platform) as a
                                        join qskj.R_Alarm_Type b on a.alarmType=b.typeId
                                        where ((busOrCar='b' and platform='ZAKJ') or (busOrCar='c' and (platform='RUIMING' or platform='zl'))) 
                                        group by b.qskjName,b.qskjId; ";
                        DataTable res2 = MySqlHelper.GetDataTable(CommandType.Text, sql2);
                        foreach (DataRow item in res2.Rows)
                        {
                            AlarmVehicleCompanyByTenToday type = new AlarmVehicleCompanyByTenToday();
                            type.time = startTime.ToString("yyyy-MM-dd");
                            type.company = item["qskjName"] == DBNull.Value ? "" : item["qskjName"].ToString();
                            type.qskjId = item["qskjId"] == DBNull.Value ? 0 : Convert.ToInt32(item["qskjId"]);
                            type.num = Convert.ToInt32(item["num"]);
                            res.Add(type);
                            var avn = depList1.Where(p => p.dep == dep.dep && p.company == type.company).FirstOrDefault();
                            int alarmvehnum = avn == null ? 0 : avn.num;//报警车辆数
                            string sqlIns = $@"insert into qskjrecord.D_FilialeData(company,filialename,num,datetime,qskjName,qskjId,vehnum,alarmvehnum)values('{dep.company}','{dep.dep}',{type.num},'{startTime.ToString("yyyy-MM-dd")}','{type.company}',{type.qskjId},{dep.num},{alarmvehnum})";
                            MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIns);
                        }

                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "2.9 近10天四公司车辆报警柱状图详情---1写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        /// <summary>
        /// 6.2用户分析每隔一个小时统计整点用户在线数
        /// </summary>
        /// <param name="dts"></param>
        /// <param name="dte"></param>
        public static void UpdateUserDayData(DateTime dts, DateTime dte)
        {
            List<CurrentAlarmType> types = new List<CurrentAlarmType>();
            DateTime startTime = dte.AddHours(-1);//当天0点
            DateTime endTime = dte;//当前时间
            for (DateTime time = startTime; time < endTime; time = time.AddHours(1))
            {
                try
                {
                    string dtStr = startTime.ToString("yyyyMMdd");
                    string sqlf = $@"select username from qskj.S_UserLoginRecord where createtime>='{time.ToString("yyyy-MM-dd HH:mm:ss")}' and createtime<'{time.AddHours(1).ToString("yyyy-MM-dd HH:mm:ss")}' and username is not null group by username;";
                    DataTable resf = MySqlHelper.GetDataTable(CommandType.Text, sqlf);
                    int num1 = resf.Rows.Count;

                    string sqlIn = $@"insert into qskj.D_UserToDayHour(hourNum,typeNum,dayDate) values({num1},0,'{time.ToString("yyyy-MM-dd HH:mm:ss")}')";
                    int res = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                    if (res == 0)
                    {
                        Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日每隔一个小时统计整点用户在线数写入结果：" + res);
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "当日每隔一个小时统计整点用户在线数写入异常：" + JsonConvert.SerializeObject(e));
                }
            }
        }
        public static void UpdateFilialeAlarmDayData(DateTime dts)
        {
            //int[] qskjType = new int[] { 3, 9, 1, 2 };//qskjId子类3-疲劳，9-超速，1-抽烟，2-打电话
            List<string> qskjType = new List<string>();
            string sqlType = $@"select  qskjId,qskjName,threeClassId from qskj.R_Alarm_Type group by qskjId,qskjName,threeClassId";
            DataTable dtType = MySqlHelper.GetDataTable(CommandType.Text, sqlType);
            foreach (DataRow dr in dtType.Rows)
            {
                if (dr["qskjId"] != DBNull.Value)
                {
                    qskjType.Add(dr["qskjId"].ToString());
                }
            }
            string dtStr = dts.ToString("yyyyMMdd");
            try
            {
                foreach (var qskjTypeItem in qskjType)
                {
                    List<AlarmVehicleCompanyByToday> res = new List<AlarmVehicleCompanyByToday>();
                    {
                        //string sqlQskjType = $@" select aa.dep,bb.a from (
                        //                     select dep from qskjrecord.B_BusZT{dtStr} group by dep)aa
                        //                     left join (
                        //                     select dep,count(*)as a  from qskjrecord.B_Vehicle_Alarm{dtStr} a
                        //                     join qskjrecord.B_BusZT{dtStr} b on a.vehicleNo=b.car_numbers
                        //                     join qskj.R_Alarm_Type c on a.alarmType=c.typeId
                        //                     where ((a.platform ='RUIMING' and c.busOrCar='c')or(a.platform ='ZAKJ' and c.busOrCar='b')) and c.qskjId={qskjTypeItem}
                        //                     group by b.dep)bb on aa.dep=bb.dep";
                        //DataTable resQskjType = MySqlHelper.GetDataTable(CommandType.Text, sqlQskjType);
                        //foreach (DataRow item in resQskjType.Rows)
                        //{
                        //    AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                        //    type.time = dts.ToString("yyyy-MM-dd");
                        //    type.company = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                        //    type.num = item["a"] == DBNull.Value ? 0 : Convert.ToInt32(item["a"]);
                        //    res.Add(type);
                        //}
                        {
                            string sqlQskjType = $@" select dep from qskjrecord.B_BusZT{dtStr} group by dep ";
                            DataTable resQskjType = MySqlHelper.GetDataTable(CommandType.Text, sqlQskjType);
                            foreach (DataRow item in resQskjType.Rows)
                            {
                                AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                type.time = dts.ToString("yyyy-MM-dd");
                                type.company = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                                type.num = 0;
                                res.Add(type);
                            }
                        }
                        {
                            string sqlQskjType = $@"select e.dep dep,count(*)as a from (select aa.* from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                     join (
                                        select alarmid,vehicleNo,max(id)id from qskjrecord.B_Vehicle_Alarm{dtStr} group by alarmId,vehicleNo
                                     ) ab on aa.id=ab.id) as a
                                     join qskj.B_Taxi b on a.vehicleNo=b.license_plate
                                     join qskj.R_Alarm_Type c on a.alarmType=c.typeId
									 left join qskjrecord.B_BusZT{dtStr} e on b.license_plate=e.car_numbers
                                     where ( c.busOrCar='c') and c.qskjId={qskjTypeItem} and a.alarmTime>='{dts.ToString("yyyy-MM-dd")}'  and a.alarmTime<'{dts.AddDays(1).ToString("yyyy-MM-dd")}' 
                                     group by e.dep";
                            DataTable resQskjType = MySqlHelper.GetDataTable(CommandType.Text, sqlQskjType);
                            foreach (DataRow item in resQskjType.Rows)
                            {
                                AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                type.time = dts.ToString("yyyy-MM-dd");
                                type.company = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                                type.num = item["a"] == DBNull.Value ? 0 : Convert.ToInt32(item["a"]);
                                res.Add(type);
                            }
                        }
                        {
                            string sqlQskjType = $@"select e.dep dep,count(*)as a  from (select aa.* from qskjrecord.B_Vehicle_Alarm{dtStr} aa
                                     join (
                                        select alarmid,vehicleNo,max(id)id from qskjrecord.B_Vehicle_Alarm{dtStr} group by alarmId,vehicleNo
                                     ) ab on aa.id=ab.id) as a
                                     join qskj.B_Bus b on a.vehicleNo=b.license_plate
                                     join qskj.R_Alarm_Type c on a.alarmType=c.typeId
									 left join qskjrecord.B_BusZT{dtStr} e on b.license_plate=e.car_numbers
                                     where ( c.busOrCar='b') and c.qskjId={qskjTypeItem} and a.alarmTime>='{dts.ToString("yyyy-MM-dd")}'  and a.alarmTime<'{dts.AddDays(1).ToString("yyyy-MM-dd")}' 
                                     group by e.dep";
                            DataTable resQskjType = MySqlHelper.GetDataTable(CommandType.Text, sqlQskjType);
                            foreach (DataRow item in resQskjType.Rows)
                            {
                                AlarmVehicleCompanyByToday type = new AlarmVehicleCompanyByToday();
                                type.time = dts.ToString("yyyy-MM-dd");
                                type.company = item["dep"] == DBNull.Value ? "" : item["dep"].ToString();
                                type.num = item["a"] == DBNull.Value ? 0 : Convert.ToInt32(item["a"]);
                                res.Add(type);
                            }
                        }
                        res = res.GroupBy(p => p.company).Select(p => new AlarmVehicleCompanyByToday { company = p.Key, num = p.Sum(x => x.num), time = dts.ToString("yyyy-MM-dd") }).ToList();
                        foreach (var item in res)
                        {
                            string sqlIn = $@"insert into qskj.D_AlarmChildTypeVehicleFilialeData(filialeName,dataNum,typeId,dataType,dayDate)values('{item.company}',{item.num},{qskjTypeItem},'天','{item.time}')";
                            int resIn = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                        }
                    }

                }
            }
            catch (Exception e)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "车队当日子类报警总数数据汇总写入异常：" + JsonConvert.SerializeObject(e));
            }
        }
        public static void UpdateBusCarOnlineData()
        {
            try
            {
                CSRedisClient cSRedisClient4 = RedisHelp.GetInstance4();
                decimal busOnline = 0, carOnline = 0, serviceload = 0, memoryload = 0, ruimingerrornum = 0, zakjerrornum = 0;
                string sqlType = $@"select busonline,taxionline,serviceload,memoryload,ruimingerrornum,zakjerrornum from qskj.B_AlarmConfig order by id desc limit 1;";
                DataTable dtType = MySqlHelper.GetDataTable(CommandType.Text, sqlType);
                foreach (DataRow dr in dtType.Rows)
                {
                    if (dr["busonline"] != DBNull.Value)
                    {
                        busOnline = Convert.ToDecimal(dr["busonline"]);
                    }
                    if (dr["taxionline"] != DBNull.Value)
                    {
                        carOnline = Convert.ToDecimal(dr["taxionline"]);
                    }
                    if (dr["serviceload"] != DBNull.Value)
                    {
                        serviceload = Convert.ToDecimal(dr["serviceload"]);
                    }
                    if (dr["memoryload"] != DBNull.Value)
                    {
                        memoryload = Convert.ToDecimal(dr["memoryload"]);
                    }
                    if (dr["ruimingerrornum"] != DBNull.Value)
                    {
                        ruimingerrornum = Convert.ToDecimal(dr["ruimingerrornum"]);
                    }
                    if (dr["zakjerrornum"] != DBNull.Value)
                    {
                        zakjerrornum = Convert.ToDecimal(dr["zakjerrornum"]);
                    }
                }
                List<VehicleOnline> busonline = new List<VehicleOnline>();
                List<VehicleOnline> caronline = new List<VehicleOnline>();
                #region 整理在离线数据
                string busR = cSRedisClient4.Get("bus");
                if (string.IsNullOrEmpty(busR))
                {
                    string sqlBus = $@"select license_plate from qskj.B_Bus ;";
                    DataTable dtBus = MySqlHelper.GetDataTable(CommandType.Text, sqlBus);
                    foreach (DataRow bus in dtBus.Rows)
                    {
                        VehicleOnline vehicle = new VehicleOnline();
                        vehicle.vehicle = bus["license_plate"] == DBNull.Value ? "" : bus["license_plate"].ToString();
                        string s = cSRedisClient4.Get(vehicle.vehicle);
                        vehicle.online = s;
                        busonline.Add(vehicle);
                    }
                    cSRedisClient4.Set("bus", JsonConvert.SerializeObject(busonline), new TimeSpan(12, 0, 0));
                }
                else
                {
                    busonline = JsonConvert.DeserializeObject<List<VehicleOnline>>(busR);
                    for (int i = 0; i < busonline.Count; i++)
                    {
                        var d = busonline[i];
                        string s = cSRedisClient4.Get(d.vehicle);
                        d.online = s;
                    }
                }
                string carR = cSRedisClient4.Get("car");
                if (string.IsNullOrEmpty(carR))
                {
                    string sqlCar = $@"select license_plate from qskj.B_Taxi ;";
                    DataTable dtCar = MySqlHelper.GetDataTable(CommandType.Text, sqlCar);
                    foreach (DataRow car in dtCar.Rows)
                    {
                        VehicleOnline vehicle = new VehicleOnline();
                        vehicle.vehicle = car["license_plate"] == DBNull.Value ? "" : car["license_plate"].ToString();
                        string s = cSRedisClient4.Get(vehicle.vehicle);
                        vehicle.online = s;
                        caronline.Add(vehicle);
                    }
                    cSRedisClient4.Set("car", JsonConvert.SerializeObject(caronline), new TimeSpan(12, 0, 0));
                }
                else
                {
                    caronline = JsonConvert.DeserializeObject<List<VehicleOnline>>(carR);
                    for (int i = 0; i < caronline.Count; i++)
                    {
                        var d = caronline[i];
                        string s = cSRedisClient4.Get(d.vehicle);
                        d.online = s;
                    }
                }
                #endregion
                decimal busOn = busonline.Where(p => p.online == "online").Count();
                decimal carOn = caronline.Where(p => p.online == "online").Count();
                decimal busOnL = busOn / busonline.Count;
                decimal carOnL = carOn / caronline.Count;
                if (busOnL * 100 < busOnline)
                {
                    string sqlInBus = $@"insert into qskj.B_ResourceAlarm(alarmtime,content,contentDes,createtime,createuser)
                                        values(now(),'大巴车在线率{Math.Round(busOnL * 100, 2)}%低于{busOnline}%','大巴车在线率{Math.Round(busOnL * 100, 2)}%低于{busOnline}%',now(),'QskjTotalData')";
                    var resInBus = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlInBus);
                }
                if (carOnL * 100 < carOnline)
                {
                    string sqlInCar = $@"insert into qskj.B_ResourceAlarm(alarmtime,content,contentDes,createtime,createuser)
                                        values(now(),'出租车在线率{Math.Round(carOnL * 100, 2)}%低于{carOnline}%','出租车在线率{Math.Round(carOnL * 100, 2)}%低于{carOnline}%',now(),'QskjTotalData')";
                    var resInCar = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlInCar);
                }
                ServerInstance cpuUsed = JsonConvert.DeserializeObject<ServerInstance>(GetServerResourceCpu().Result), memoryUsed = JsonConvert.DeserializeObject<ServerInstance>(GetServerResourceMemory().Result);
                //string cpuUsed = "55.73", memoryUsed = "82.35";
                foreach (var item in cpuUsed?.data?.result)
                {
                    string instance = item.metric?.instance;
                    decimal cpuUsedL = Convert.ToDecimal(item.value[1]);
                    if (serviceload < cpuUsedL)
                    {
                        //CPU使用率
                        string sqlInCar = $@"insert into qskj.B_ResourceAlarm(alarmtime,content,contentDes,createtime,createuser)
                                        values(now(),'服务器{instance}CPU使用率{Math.Round(cpuUsedL, 2)}%大于{serviceload}%','服务器{instance}CPU使用率{Math.Round(cpuUsedL, 2)}%大于{serviceload}%',now(),'QskjTotalData')";
                        var resInCar = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlInCar);
                    }
                }
                foreach (var item in memoryUsed?.data?.result)
                {
                    string instance = item.metric?.instance;
                    decimal memoryUsedL = Convert.ToDecimal(item.value[1]);
                    if (memoryload < memoryUsedL)
                    {
                        //内存使用率
                        string sqlInCar = $@"insert into qskj.B_ResourceAlarm(alarmtime,content,contentDes,createtime,createuser)
                                        values(now(),'服务器{instance}内存使用率{Math.Round(memoryUsedL, 2)}%大于{memoryload}%','服务器{instance}内存使用率{Math.Round(memoryUsedL, 2)}%大于{memoryload}%',now(),'QskjTotalData')";
                        var resInCar = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlInCar);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "车辆在线率统计异常：" + JsonConvert.SerializeObject(e));
            }
        }
        #region 查询服务资源

        /// <summary>
        /// 服务器内存使用率
        /// </summary>
        /// <returns></returns>
        public static async Task<string> GetServerResourceMemory()
        {
            var client = new HttpClient();
            var request = new HttpRequestMessage(HttpMethod.Get, "http://192.168.3.78:9090/api/v1/query?query=%28node_memory_MemTotal_bytes-%28node_memory_MemFree_bytes%2B+node_memory_Cached_bytes+%2B+node_memory_Buffers_bytes%29%29+%0A%0A%2F+node_memory_MemTotal_bytes+*+100");
            var response = await client.SendAsync(request);
            response.EnsureSuccessStatusCode();
            string res = await response.Content.ReadAsStringAsync();
            return res;
        }
        /// <summary>
        /// 服务器CPU使用率
        /// </summary>
        /// <returns></returns>
        public static async Task<string> GetServerResourceCpu()
        {
            var client = new HttpClient();
            var request = new HttpRequestMessage(HttpMethod.Get, "http://192.168.3.78:9090/api/v1/query?query=100+*+%281+-+sum%28rate%28node_cpu_seconds_total%7Bmode%3D%22idle%22%7D%5B5m%5D%29%29+by+%28instance%29+%2F+sum%28rate%28node_cpu_seconds_total%5B5m%5D%29%29+by+%28instance%29%29%0A");
            var response = await client.SendAsync(request);
            response.EnsureSuccessStatusCode();
            string res = await response.Content.ReadAsStringAsync();
            return res;
        }
        #endregion
        /// <summary>
        /// 
        /// </summary>
        public static async void UpdateNetworkFilesData()
        {
            try
            {
                string tokeninfo = HttpHelper.GetToken().Result;
                TokenInfo token1 = new TokenInfo();
                token1 = JsonConvert.DeserializeObject<TokenInfo>(tokeninfo);

                List<NetworkData> data = new List<NetworkData>();
                string sql = $@"select a.url,a.id from qskj.B_NetworkInspector a
                                left join qskj.B_NetworkFileUrl b on a.id=b.bniId
                                where b.url is null and a.url!='' and a.url is not null and a.createtime>='{DateTime.Now.AddDays(-5).ToString("yyyy-MM-dd")}';";
                DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
                foreach (DataRow dr in dt.Rows)
                {
                    NetworkData nd = new NetworkData();
                    nd.key = dr["url"] == DBNull.Value ? "" : dr["url"].ToString();
                    nd.id = dr["id"].ToString();
                    data.Add(nd);
                }
                foreach (var item in data)
                {
                    if (!string.IsNullOrWhiteSpace(item.key))
                    {
                        string[] s = item.key.Split(',');
                        foreach (var k in s)
                        {
                            try
                            {
                                if (k.Contains("Request body too large"))
                                {
                                    continue;
                                }

                                var client = new HttpClient();
                                var request = new HttpRequestMessage(HttpMethod.Get, $@"http://192.168.3.231:40001/api/v1/Qs_NetworkInspector/GetFileUrl?key={k}&pid={item.id}");
                                request.Headers.Add("Authorization", "Bearer " + token1?.token);
                                var response = await client.SendAsync(request);
                                response.EnsureSuccessStatusCode();
                                string res = await response.Content.ReadAsStringAsync();

                            }
                            catch (Exception e)
                            {
                                string sqlIn = $@"insert into B_NetworkFileUrl(bniId,url,type,createtime,pkey)values({item.id},'查询异常：{e.Message}',1,now(),'{k}')";
                                MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlIn);
                                Console.WriteLine("查询视图库地址异常：" + JsonConvert.SerializeObject(e));
                            }
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "稽查数据上传证据视图库地址查询异常：" + JsonConvert.SerializeObject(e));
            }
        }
        /// <summary>
        /// 违规复查报警数据统计数据生产
        /// </summary>
        public static void SumAlarmAndFile()
        {
            try
            {
                string strDt = DateTime.Now.AddDays(-1).ToString("yyyyMMdd");
                string sql = $@"INSERT INTO qskj.D_AlarmAndFile (DataNum,dayDate,createtime)
                            SELECT SUM(IsShow),'{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}',NOW()  FROM qskjrecord.B_Vehicle_AlarmAndFile{strDt} WHERE IsShow =1;";
                int num = MySqlHelper.ExecuteNonQuery(CommandType.Text, sql);
                if (num == 0)
                {
                    Console.WriteLine("违规复查报警数据统计数据生产失败！");
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("违规复查报警数据统计数据生产失败！" + e.Message);
            }
        }
        /// <summary>
        /// 车队在线数据统计数据生产
        /// </summary>
        public static void SumFilialeOnline()
        {
            try
            {
                List<CompanyFilialeVehicle> list = new List<CompanyFilialeVehicle>();
                string strDt = DateTime.Now.AddDays(-1).ToString("yyyyMMdd");
                Stopwatch stopwatch = new Stopwatch();
                stopwatch.Start();
                string sql = $@"select a.car_assets_name_name company, a.dep,b.license_plate from qskjrecord.B_BusZT{strDt} a
                            join qskj.B_Taxi b on a.car_numbers=b.license_plate
                            union all
                            select a.car_assets_name_name, a.dep,b.license_plate from qskjrecord.B_BusZT{strDt} a
                            join qskj.B_Bus b on a.car_numbers=b.license_plate;";
                DataTable dt = MySqlHelper.GetDataTable(CommandType.Text, sql);
                foreach (DataRow item in dt.Rows)
                {
                    CompanyFilialeVehicle vehicle = new CompanyFilialeVehicle();
                    vehicle.CompanyName = item["company"].ToString();
                    vehicle.FilialeName = item["dep"].ToString();
                    vehicle.VehicleNo = item["license_plate"].ToString();
                    {
                        string sqlOnOff = $@"select devStatus from qskjrecord.B_Device_Status{strDt} where vehicleNo='{vehicle.VehicleNo}' and devStatus='online' order by id desc limit 1;";
                        object res = MySqlHelper.ExecuteScalar(CommandType.Text, sqlOnOff);
                        vehicle.OnOffline = res == null ? "offline" : res.ToString();
                    }
                    list.Add(vehicle);
                }
                stopwatch.Stop();
                Console.WriteLine(stopwatch.ElapsedMilliseconds);
                List<CompanyFilialeVehicle> listCf = list.GroupBy(p => new { p.CompanyName, p.FilialeName }).Select(p => new CompanyFilialeVehicle { CompanyName = p.Key.CompanyName, FilialeName = p.Key.FilialeName }).ToList();
                foreach (var item in listCf)
                {
                    FilialeVehicleOnline fvo = new FilialeVehicleOnline();
                    fvo.CompanyName = item.CompanyName;
                    fvo.FilialeName = item.FilialeName;
                    int vehicleNo = list.Where(p => p.CompanyName == item.CompanyName && p.FilialeName == item.FilialeName).Count();
                    int num = list.Where(p => p.CompanyName == item.CompanyName && p.FilialeName == item.FilialeName && p.OnOffline == "online").Count();
                    string sqlOnOff = $@"insert into qskjrecord.D_FilialeOnlineData(company,filialename,vehnum,num,datetime)values('{item.CompanyName}','{item.FilialeName}',{vehicleNo},{num},'{DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd")}')";
                    int res = MySqlHelper.ExecuteNonQuery(CommandType.Text, sqlOnOff);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("车队在线数据统计数据生产失败！" + e.Message);
            }
        }
        /// <summary>
        /// 定时补充第一次上线车辆车队信息
        /// </summary>
        public static void UpdateVehicleOnlineData()
        {
            try
            {
                string strDt = DateTime.Now.AddDays(-1).ToString("yyyyMMdd");
                string sql = $@"UPDATE qskjrecord.D_VehicleOnlineData a
                                join qskjrecord.B_BusZT{strDt} b on a.vehicleNo =b.car_numbers 
                                set a.filialename =b.dep 
                                WHERE a.filialename IS null;";
                int res = MySqlHelper.ExecuteNonQuery(CommandType.Text, sql);
            }
            catch (Exception e)
            {
                Console.WriteLine("定时补充第一次上线车辆车队信息失败！" + e.Message);
            }
        }
    }
}
